package net.bat.repository;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadFile {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO 自动生成方法存根
		File file = new File("E:/01.xlsx");
		if (!file.exists()) {
			System.out.println("文件不存在");
			return;
		}
		ReadFile rf = new ReadFile();
		List<String[]> list = rf.readExcel(file);
		System.out.println(list.size());
		String[] strs = null;
		for (int i = 0; i < list.size(); i++) {
			for (int j = 0; j < list.get(i).length; j++) {
				// list.get(i)

			}

		}
	}

	/**
	 * 读取Excel数据
	 * 
	 * @param file
	 * @return
	 */
	/**
	 * 读取Excel数据
	 * 
	 * @param file
	 */
	public static List<String[]> readExcel(File file) {
		List<String[]> list = new ArrayList<String[]>();
		try {
			InputStream inputStream = new FileInputStream(file);
			String fileName = file.getName();
			Workbook wb = null;
			if (fileName.endsWith("xls")) {
				wb = new HSSFWorkbook(inputStream);// 解析xls格式
			} else if (fileName.endsWith("xlsx")) {
				wb = new XSSFWorkbook(inputStream);// 解析xlsx格式
			}
			Sheet sheet = wb.getSheetAt(0);// 第一个工作表

			int firstRowIndex = sheet.getFirstRowNum();
			int lastRowIndex = sheet.getLastRowNum();
			// sheet.shiftRows(1, lastRowIndex, -1);
			// sheet.removeRow(sheet.getRow(lastRowIndex));
			// int aaa=sheet.getLastRowNum();
			// System.out.println(aaa);
			for (int rIndex = firstRowIndex + 1; rIndex <= lastRowIndex; rIndex++) {
				Row row = sheet.getRow(rIndex);

				if (row != null) {
					int firstCellIndex = row.getFirstCellNum();
					int lastCellIndex = row.getLastCellNum();
					String[] values = new String[lastCellIndex];
					for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
						Cell cell = row.getCell(cIndex);
						String value = "";
						if (cell != null) {
							value = getValue(cell);
							//System.out.print(value + "\t");
							values[cIndex] = value;
						}
					}
					list.add(values);
				//	System.out.println();
				}
			}

		} catch (FileNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (IOException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}

		return list;
	}

	// 解决excel类型问题，获得数值
	public static String getValue(Cell cell) {
		String value = "";
		if (null == cell) {
			return value;
		}
		switch (cell.getCellType()) {
		// 数值型

		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				// 如果是date类型则 ，获取该cell的date值
				Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				value = format.format(date);
			} else {
				// 纯数字
				/*
				 * BigDecimal big=new BigDecimal(cell.getNumericCellValue());
				 * value = big.toString();
				 */
				value = String.valueOf(cell.getNumericCellValue());
				if (value.indexOf('E') > -1) {
					value = String.valueOf(new DecimalFormat("#.##").format(cell.getNumericCellValue()));
					// c4w JDK8 编译 value此处一定非空,不需要检查
				} else if (!"".equals(value.trim())) {
					// 解决1234.0 去掉后面的.0
					String[] item = value.split("[.]");
					if ((1 < item.length) && "0".equals(item[1])) {
						value = item[0];
					}
				}
			}
			break;
		// 字符串类型
		case HSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue().toString();
			break;
		// 公式类型
		case HSSFCell.CELL_TYPE_FORMULA:
			// 读公式计算值
			value = String.valueOf(cell.getNumericCellValue());
			if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
				value = cell.getStringCellValue().toString();
			}
			break;
		// 布尔类型
		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = " " + cell.getBooleanCellValue();
			break;
		// 空值
		case HSSFCell.CELL_TYPE_BLANK:
			value = "";
			// LogUtil.getLogger().error("excel出现空值");
		//	System.out.println("excel出现空值");
			break;
		// 故障
		case HSSFCell.CELL_TYPE_ERROR:
			value = String.valueOf(cell.getErrorCellValue());
			// LogUtil.getLogger().error("excel出现故障");
			break;
		default:
			value = cell.getStringCellValue().toString();
		}
		if ("null".endsWith(value.trim())) {
			value = "";
		}
		return value;
	}
}
